CSE 444 Midterm

Autumn 1999

Name___________________________________

 

 

 

Please define the following terms in a three sentences or less:
  1. [10 points] Referential integrity constraints

    2.  

     

     

     

     

     

     

     

     

     

     

  2. [10 points] Correlated queries

 

 

 

[20 points] Decompose R(A,B,C,D,E) with functional dependencies Aà B, Bà C, Cà D into BCNF. Justify your answer, show your work, and please circle the final relations in your decomposition

 

 

Given the following schema for a library, give queries for the questions below. Note that the last question asks for a query in relational algebra.

Book(ISBN, author, title, subject)

Borrowed(book_ISBN, borrowers_card_number, due_date)

Library_Patron(card_number, first_name, last_name, phone_number)

Book_Request(book_ISBN, requesters_card_number)

 

  1. [15 points] For each book that has been both borrowed and requested, give the phone number of the borrower and the requester

    2.  

     

     

     

  2. [10 points] Give a SQL query for how many times have books titled A First Course in Database Systems been borrowed?

    3.  

     

     

     

  3. [15 points] Give a SQL query that produces the set of pairs (a, n), where n is the number of times that Alon Levy has borrowed a book authored by a.

    4.  

     

     

     

  4. [5 points] Give a SQL query that produces the set of pairs (borrower, number) such that "number" is the number of books borrowed by the "borrower" and that satisfy the following conditions:

    5.  

     

     

     

  5. [15 points] Give a relational algebra query to list the titles of all books borrowed by people whose last name is Pottinger